from flask import Flask,render_template
import sqlite3
import pymysql
app = Flask(__name__)

arrtimelist = [0,7,15,30,60,90,180]
arrangelist = [0,1000,2000,3000,5000,10000]

@app.route('/')
def index():
    return render_template("index.html")

@app.route('/index')
def home():
    return index()

@app.route('/project')
def project():
    #datalist = []
    #conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
    #cur = conn.cursor()
    #sql = "select * from project"
    #data = cur.execute(sql)
    #for item in data:
    #    datalist.append(item)
    #cur.close()
    #conn.close()

    conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
    cur = conn.cursor()

    tslist = []
    # 读取数据库，把数据逐个抠下来,要对上图表引用的数据格式
    sql = "select time from timestatus group by time"
    cur.execute(sql)
    timeline = cur.fetchall()

    timelist = []
    for ll in timeline:
        timelist.append(ll[0])

    sql = "select status from timestatus group by status"
    cur.execute(sql)
    statusline = cur.fetchall()
    statuslist = []
    for ll in statusline:
        statuslist.append(ll[0])
        #statuslist.append(ll[0].decode('gbk','ignore'))
        #print(str(type(statuslist[0])))

    for ll in statuslist:
        sql = "select sum from timestatus where status = '%s'" % (ll)
        cur.execute(sql)
        sll = cur.fetchall()
        realist = []
        for index in sll:
            realist.append(index[0])
        tslist.append(realist)
    #for ll in tslist:
    #    print(str(ll))
    #print(timelist)
    #print(statuslist)

    prizelist = []


    #获得各类造价的项目数量
    sql = "select count(pid) from project where 0 = midprize"
    cur.execute(sql)
    prizelist.append(cur.fetchone()[0])
    for i in range(0,5):
        sql = "select count(pid) from project where %d < midprize and %d >= midprize" % (arrangelist[i],arrangelist[i+1])
        #print(sql)
        cur.execute(sql)
        prizelist.append(cur.fetchone()[0])

    sql = "select count(pid) from project where midprize > 10000"
    cur.execute(sql)
    prizelist.append(cur.fetchone()[0])

    datelist = []

    for i in range(0, 6):
        sql = "select count(pid) from project where %d < time and %d >=time" % (arrtimelist[i], arrtimelist[i + 1])
        cur.execute(sql)
        datelist.append(cur.fetchone()[0])
    sql = "select count(pid) from project where 180 < time"
    cur.execute(sql)
    datelist.append(cur.fetchone()[0])

    sql = r'select avg(num),sum(num) from project group by DATE_FORMAT(date,"%Y-%m")'
    cur.execute(sql)
    numlist = cur.fetchall()
    numsum = []
    numavg = []
    for ll in numlist:
        numavg.append(float(ll[0]))
        numsum.append(float(ll[1]))
    print(str(numsum))
    print(str(numavg))
    cur.close()
    conn.close()

    return render_template("project.html",timelist=timelist,statuslist=statuslist,tslist = tslist,prizelist=prizelist,datelist=datelist,numsum=numsum,numavg=numavg)

@app.route('/user')
def user():
    conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
    cur = conn.cursor()

    #,avg(pronum),avg(exnum),avg(income),
    sql = "select count(uid),avg(income),avg(pronum),avg(exnum),lv from user group by lv order by lv"
    cur.execute(sql)
    lvlist = cur.fetchall()
    sumlist = []
    incomelist = []
    prolist = []
    exlist = []
    for ll in lvlist:
        sumlist.append(ll[0])
        incomelist.append(ll[1])
        prolist.append(float(ll[2]))
        exlist.append(float(ll[3]))
    #print("income:"+str(incomelist))
    #print("pro:"+str(prolist))
    #print("ex:"+str(exlist))
    cur.close()
    conn.close()
    return render_template("user.html",sumlist=sumlist,incomelist=incomelist,prolist=prolist,exlist=exlist)

@app.route('/sample')
def sample():
    conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
    cur = conn.cursor()

    timelist = []

    for i in range(0,6):
        sql = "select count(sid) from sample where %d < time and %d >=time" % (arrtimelist[i],arrtimelist[i+1])
        cur.execute(sql)
        timelist.append(cur.fetchone()[0])
    sql = "select count(sid) from sample where 180 < time"
    cur.execute(sql)
    timelist.append(cur.fetchone()[0])

    prizelist = []

    for i in range(0,5):
        sql = "select count(sid) from sample where %d < midprize and %d >= midprize" % (arrangelist[i],arrangelist[i+1])
        print(sql)
        cur.execute(sql)
        prizelist.append(cur.fetchone()[0])

    sql = "select count(sid) from sample where midprize > 10000"
    cur.execute(sql)
    prizelist.append(cur.fetchone()[0])

    cur.close()
    conn.close()
    return render_template("sample.html",timelist=timelist,prizelist=prizelist)

@app.route('/list')
def list():
    conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
    cur = conn.cursor()
    cur.execute("select * from hotlist")
    alist = []
    alist = cur.fetchall()

    cur.close()
    conn.close()
    return render_template("list.html",alist=alist)

@app.route('/word')
def word():
    return render_template("word.html")

if __name__ == '__main__':
    app.run()